"""
邮件配置表结构迁移脚本
"""

from sqlalchemy import create_engine, text
from config import settings

def migrate_email_configs_table():
    """迁移邮件配置表结构"""
    engine = create_engine(settings.database_url)
    
    with engine.connect() as conn:
        # 开始事务
        trans = conn.begin()
        
        try:
            # 检查email_configs表是否存在
            result = conn.execute(text("""
                SELECT COUNT(*) 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name = 'email_configs'
            """))
            
            table_exists = result.scalar() > 0
            
            if table_exists:
                print("邮件配置表已存在，检查是否需要更新字段...")
                
                # 检查是否有新字段
                result = conn.execute(text("""
                    SELECT COUNT(*) 
                    FROM information_schema.columns 
                    WHERE table_schema = DATABASE() 
                    AND table_name = 'email_configs' 
                    AND column_name = 'from_email'
                """))
                
                has_from_email = result.scalar() > 0
                
                if not has_from_email:
                    print("更新邮件配置表字段...")
                    
                    # 添加新字段
                    conn.execute(text("""
                        ALTER TABLE email_configs 
                        ADD COLUMN from_email VARCHAR(255) NOT NULL DEFAULT '',
                        ADD COLUMN from_name VARCHAR(100),
                        ADD COLUMN encryption VARCHAR(20) NOT NULL DEFAULT 'none',
                        ADD COLUMN timeout INT NOT NULL DEFAULT 30,
                        ADD COLUMN description TEXT,
                        ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE,
                        MODIFY COLUMN smtp_host VARCHAR(255) NOT NULL,
                        MODIFY COLUMN username VARCHAR(255) NOT NULL,
                        MODIFY COLUMN password TEXT NOT NULL
                    """))
                    
                    # 删除旧字段（分别执行）
                    try:
                        conn.execute(text("ALTER TABLE email_configs DROP COLUMN use_ssl"))
                    except:
                        pass  # 字段可能不存在

                    try:
                        conn.execute(text("ALTER TABLE email_configs DROP COLUMN use_starttls"))
                    except:
                        pass  # 字段可能不存在
                    
                    # 添加索引
                    conn.execute(text("""
                        CREATE INDEX idx_email_configs_user_name ON email_configs(user_id, name)
                    """))
                    conn.execute(text("""
                        CREATE INDEX idx_email_configs_user_active ON email_configs(user_id, is_active)
                    """))
                    conn.execute(text("""
                        CREATE INDEX idx_email_configs_created_at ON email_configs(created_at)
                    """))
                    
                    print("邮件配置表字段更新完成")
                else:
                    print("邮件配置表字段已是最新")
            else:
                print("邮件配置表不存在，将由SQLAlchemy自动创建")
            
            # 提交事务
            trans.commit()
            print("邮件配置表结构迁移完成！")
            
        except Exception as e:
            # 回滚事务
            trans.rollback()
            print(f"迁移失败: {e}")
            raise

if __name__ == "__main__":
    migrate_email_configs_table()
